#!/bin/bash
echo "开始创建ODS层数据库的表结构！"
db1="ods02"
db2="dw02"
db3="dm02"

echo "开始创建数据库================"
echo "开始创建数据库db1!==========="
hive -e "create database if not exists ${db1};"
echo $?
echo "完成创建数据库db1!==========="

echo "开始创建数据库db2!==========="
hive -e "create database if not exists ${db2};"
echo $?
echo "完成创建数据库db2!==========="

echo "开始创建数据库db3!==========="
hive -e "create database if not exists ${db3};"
echo $?
echo "完成创建数据库db3!==========="

echo "完成创建数据库================"



02createodstables.sh
#!/bin/bash

db1="ods02"
t1="ods_cust_info_02"  #客户基本信息表
echo "创建ods层的落地表"
c_t="create table if not exists ${db1}.${t1}(
cust_id string,
cust_name string,
product_code int,
txt_amt int,
addrno int,
age int,
dt string
)
row format delimited fields terminated by '\t';"
#使用hive指令进行创建
echo "$c_t"|hive 
echo $?
echo "ods层客户基表已创建完成"
echo "----------------------分割线----------------------------------"


#!/bin/bash
db1="ods02"
t1="ods_cust_info_02" #客户基本信息表
echo "创建ods层的落地表${t1}"
create_table_t1="create table if not exists ${db1}.${t1}(
cust_id string,
cust_name string,
product_code int,
txt_amt int,
addrno int,
age int,
dt string)
row format delimited fields terminated by '\t';"
#使用hive指令进行创建
echo "$create_table_t1"|hive
echo $?
echo "ods层客户基本表创建完成"
echo "----------------------分割线----------------------------------"



03createdwtable.sh
#!/bin/bash
echo "创建dw层客户基表-----------------------------"
db2="dw02"
t2="dw_cust_info_02" #dw层客户基表
t3="dw_product_info_02" #产品码表
t4="dw_addr_info_02" #地址码表 
create_table_t2="create table if not exists ${db2}.${t2}(
cust_id string,
cust_name string,
product_code int,
txt_amt int,
addrno int,
age int,
dt string,
etl_dt string)
row format delimited fields terminated by '\t';"
#使用hive -e 指令进行创建
hive -e "$create_table_t2"
echo $?
echo "dw层客户基表已创建完成"

echo "创建dw层产品码表---------------------------------"
create_table_t3="create table if not exists ${db2}.${t3}(
product_code int,
product_name string,
product_type string)
row format delimited fields terminated by '\t';"
#使用hive -e 指令进行创建
hive -e "$create_table_t3"
echo $?
echo "dw层产品码表已创建完成"

echo "创建dw层地址码表-------------------------------------"
create_table_t4="create table if not exists ${db2}.${t4}(
addrno int,
addrname string,
addrcode string)
row format delimited fields terminated by ',';"
#使用hive -e 指令进行创建
hive -e "$create_table_t4"
echo $?
echo "dw层地址码表已创建完成"
echo "dw层所有表格已创建完成，进行dm层的表格的创建"

04createdmtable.sh
echo "创建dm层销售情况表-------------------------"
db3="dm02"
t5="dm_rk_02" #指标落地表
create_table_t5="create table if not exists ${db3}.${t5}(
addrname string,
product_name string,
s_amt int,
r_n int,
etl_dt string)
row format delimited fields terminated by '\t';"
#使用hive -e 指令进行创建
hive -e "$create_table_t5"
echo $?
echo "创建完成----------------------------------"




---------------导入数据-----------------------

05loaddata.sh
#!/bin/bash
echo "导入ods的数据到客户基表中"
hive -v -e "set hive.exec.mode.local.auto=true;load data local inpath '/root/yhlx01/a.txt' overwrite into table ods02.ods_cust_info_02;"
echo $?
echo "导入dw的数据到地址码表中"
hive -v -e "set hive.exec.mode.local.auto=true;load data local inpath '/root/yhlx01/c.txt' overwrite into table dw02.dw_addr_info_02;"
echo $?
echo "导入dw的数据到产品码表中"
hive -v -e "set hive.exec.mode.local.auto=true;load data local inpath '/root/yhlx01/b.txt' overwrite into table dw02.dw_product_info_02;"
echo $?
echo "统计三张来源表的数据量"
c=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from ods02.ods_cust_info_02;"`
echo "客户基表本次抽取的数据量为：${c}条"
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from dw02.dw_addr_info_02;"`
echo "地址码表本次抽取的数据量为：${c1}条"
c2=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from dw02.dw_product_info_02;"`
echo "产品码表本次抽取的数据量为：${c2}条"
echo "抽数完成，请自行验证！"

--------------------------------清洗数据-------------------------------------
06odscleartodw.sh
#!/bin/bash
#######hive数仓小项目
####创建日期：2025-04-30
####创建人员：2502全体
###版本功能：清洗ods层客户基表的数据落地到dw层
#来源表：ods_cust_info_02 落地表：dw_cust_info_02
###开始编写脚本，封装sql，需要先把sql跑通然后再进行封装
hive -v -e "set hive.exec.mode.local.auto=true;
insert overwrite table dw02.dw_cust_info_02
select 
regexp_replace(cust_id,'[^0-9]+','') cust_id,
regexp_replace(cust_name,'[^一-龥]+','') cust_name,
product_code,
txt_amt,
addrno,
age,
dt,
current_timestamp etl_dt
from ods02.ods_cust_info_02;"
echo $?
echo "---数据已清洗完成，接下来数据的验证"
c=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from ods02.ods_cust_info_02;"`
echo "客户基表ods的数据量为：${c}条"
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from dw02.dw_cust_info_02;"`
echo "dw层的数据量为：${c1}条"
if [ "$c1" -eq "$c" ];then
    echo "清洗成功"
else
    echo "清洗失败"
fi



-------------------------------清洗-----------------------
07dwcleartodm.sh

#!/bin/sh
#######hive数仓小项目
####创建日期：2025-04-30
####创建人员：2502全体
###版本功能：加工dm层指标，计算各地区各产品排名，以及销售总额
###来源表：dw_cust_info_02、dw_product_info_02、dw_addr_info_02
###落地表：dm_rk_02
###开始编写脚本
hive -v -e "set hive.exec.mode.local.auto=true;
insert overwrite table dm02.dm_rk_02
select a.*,
row_number() over(partition by addrname order by s_amt desc) r_n,
current_timestamp etl_dt
from
(select 
nvl(b.addrname,'其他地区') addrname,
nvl(c.product_name ,'其他产品') product_name,
sum(a.txt_amt) s_amt
from dw02.dw_cust_info_02 a left join 
--关联之前对从表的全字段进行去重，防止数据发散
(select 
addrno,
addrname,
addrcode
from dw02.dw_addr_info_02
group by 
addrno,
addrname,
addrcode
order by
addrno,
addrname,
addrcode
) b on a.addrno=b.addrno
left join 
(select 
product_code, 
product_name, 
product_type
from dw02.dw_product_info_02
group by 
product_code, 
product_name, 
product_type 
order  by 
product_code, 
product_name, 
product_type ) c on a.product_code=c.product_code
group by 
nvl(b.addrname,'其他地区'),
nvl(c.product_name,'其他产品')) a;"
echo $?
echo "数据已计算完成，请验证数据"
